import pandas as pd
import sqlite3


def create_table(conn, table_name):
    """创建指定题型的表"""
    cursor = conn.cursor()
    cursor.execute(f'''
        CREATE TABLE IF NOT EXISTS {table_name} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            question TEXT,
            option_a TEXT,
            option_b TEXT,
            option_c TEXT,
            option_d TEXT,
            answer TEXT
        )
    ''')
    conn.commit()


def read_excel_to_tables(excel_file_path, db_file_path):
    # 读取 Excel 文件
    df = pd.read_excel(excel_file_path)

    # 连接到 SQLite 数据库
    conn = sqlite3.connect(db_file_path)

    # 遍历 Excel 中的每一行数据
    for index, row in df.iterrows():
        question_type = 'q5'
        table_name = f"question_{question_type}"  # 根据题型命名表名

        # 创建表（如果不存在）
        create_table(conn, table_name)

        question = row['题目']
        option_a = row['选项A']
        option_b = row['选项B']
        option_c = row['选项C']
        option_d = row['选项D']
        answer = row['答案']

        # 插入数据到对应的表
        cursor = conn.cursor()
        cursor.execute(f'''
            INSERT INTO {table_name} (question, option_a, option_b, option_c, option_d, answer)
            VALUES (?,?,?,?,?,?)
        ''', (question, option_a, option_b, option_c, option_d, answer))

    # 提交更改并关闭连接
    conn.commit()
    conn.close()


if __name__ == "__main__":
    excel_file_path = r'D:\WeChat\WeChat Files\wxid_7xu4hv3onjie22\FileStorage\File\2025-04\职途题目\资料分析.xlsx'  # 替换为你的 Excel 文件路径
    db_file_path = 'db.sqlite3'  # 替换为你想要存储的数据库文件路径
    read_excel_to_tables(excel_file_path, db_file_path)